package com.Spring.SpringSpms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;
import com.Spring.SpringSpms.model.*;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;


/**
 * An implementation of the StoryDAO interface.
 * @author www.codejava.net
 *
 */
public class StoryDAOImpl implements StoryDAO {

	private JdbcTemplate jdbcTemplate;
	
	public StoryDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	


	@Override
	public void saveOrUpdate(Story s,int projectId) {
		if (s.getStory_id() > 0) {
			// update
			String sql ="UPDATE Story SET story_title=?, story_description=?, story_owner=?,"
					     +"planned_start_date=?, planned_end_date=?, actual_start_date=?,"
					     +"actual_end_date=?, complexity_id=?, status_id=?, priority_id=?,"
					     +"story_point_id=?, actual_effort=?, remaining_effort=?,"+""
					     + " acceptance_criteria=? WHERE story_id=? and project_id=?";
			     		     
					/*"UPDATE Story SET name=?, email=?, address=?, "
						+ "telephone=? WHERE Story_id=?";*/
			
			jdbcTemplate.update(sql, s.getStory_title() , s.getStory_description() ,s.getStory_owner(),
					s.getPlanned_start_date() ,s.getPlanned_end_date() ,s.getActual_start_date() 
					,s.getActual_end_date() , s.getComplexity_id() ,s.getStatus_id() ,
					s.getPriority_id(),s.getStory_point_id(),s.getActual_effort(),s.getRemaining_effort()
					,s.getAcceptance_criteria(),s.getStory_id(),projectId);
		} else {
			// insert
			
			
			String sql = "INSERT INTO Story (project_id, story_title, story_description, story_owner,"
					     +"planned_start_date , planned_end_date, actual_start_date,"
					     +"actual_end_date, complexity_id, status_id, priority_id,"
					     +"story_point_id, actual_effort, remaining_effort,"
					     + " acceptance_criteria,sprint_id) VALUES (?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?,?, ?,?,?)";

			
			jdbcTemplate.update(sql,projectId,s.getStory_title(), s.getStory_description() ,s.getStory_owner(),
					s.getPlanned_start_date() ,s.getPlanned_end_date() ,s.getActual_start_date() 
					,s.getActual_end_date() , s.getComplexity_id() ,s.getStatus_id() ,
					s.getPriority_id(),s.getStory_point_id(),s.getActual_effort(),s.getRemaining_effort()
					,s.getAcceptance_criteria(),null);
		}
		
	}

	@Override
	public void delete(int StoryId) {
		String sql = "DELETE FROM Story WHERE Story_id=?";
		jdbcTemplate.update(sql, StoryId);
	}

	@Override
	public List<Story> list() {
		String sql = "SELECT * FROM Story";
		List<Story> listStory = jdbcTemplate.query(sql, new RowMapper<Story>() {

			@Override
			public Story mapRow(ResultSet rs, int rowNum) throws SQLException {
				Story aStory = new Story();
	
			     aStory.setStory_id(rs.getInt("story_id"));	
			     aStory.setProject_id(rs.getInt("project_id"));
			     aStory.setStory_title(rs.getString("story_title"));
			     aStory.setStory_description(rs.getString("story_description"));
			     aStory.setStory_owner(rs.getString("story_owner"));
			     aStory.setPlanned_start_date(rs.getDate("planned_start_date"));
			     aStory.setPlanned_end_date(rs.getDate("planned_end_date"));
			     aStory.setActual_start_date(rs.getDate("actual_start_date"));
			     aStory.setActual_end_date(rs.getDate("actual_end_date"));
			     aStory.setComplexity_id(rs.getInt("complexity_id"));
			     aStory.setStatus_id(rs.getInt("status_id"));
			     aStory.setPriority_id(rs.getInt("priority_id"));
			     aStory.setStory_point_id(rs.getInt("story_point_id"));
			     aStory.setActual_effort(rs.getInt("actual_effort"));
			     aStory.setRemaining_effort(rs.getInt("remaining_effort"));
			     aStory.setAcceptance_criteria(rs.getString("acceptance_criteria"));
			     		     
			     
			     return aStory;
			}
			
		});
		
		return listStory;
	}
	
	@Override
	public List<Story> getUnscheduledStory()
	{
		String STORY_SQL = "Select * FROM Story where sprint_id IS NULL";
		List<Story> storyList = jdbcTemplate.query(STORY_SQL ,  new RowMapper<Story>() {

			@Override
			public Story mapRow(ResultSet rs, int rowNum) throws SQLException {
			     Story aStory = new Story();
	
			     aStory.setStory_id(rs.getInt("story_id"));
			     aStory.setStory_title(rs.getString("story_title"));
			     		     
			     
			     return aStory;
			}
			
		});

		return storyList;
	}


	@Override
	public Story get(int StoryId) {
		String sql = "SELECT * FROM Story WHERE Story_id=" + StoryId;
		return jdbcTemplate.query(sql, new ResultSetExtractor<Story>() {

			@Override
			public Story extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Story Story = new Story();
				     Story.setStory_id(rs.getInt("story_id"));	
				     Story.setProject_id(rs.getInt("project_id"));
				     Story.setStory_title(rs.getString("story_title"));
				     Story.setStory_description(rs.getString("story_description"));
				     Story.setStory_owner(rs.getString("story_owner"));
				     Story.setPlanned_start_date(rs.getDate("planned_start_date"));
				     Story.setPlanned_end_date(rs.getDate("planned_end_date"));
				     Story.setActual_start_date(rs.getDate("actual_start_date"));
				     Story.setActual_end_date(rs.getDate("actual_end_date"));
				     Story.setComplexity_id(rs.getInt("complexity_id"));
				     Story.setStatus_id(rs.getInt("status_id"));
				     Story.setPriority_id(rs.getInt("priority_id"));
				     Story.setStory_point_id(rs.getInt("story_point_id"));
				     Story.setActual_effort(rs.getInt("actual_effort"));
				     Story.setRemaining_effort(rs.getInt("remaining_effort"));
				     Story.setAcceptance_criteria(rs.getString("acceptance_criteria"));
				     		     
						return Story;
				}
				
				return null;
			}
			
		});
		
	}

	@Override
	public void MoveTo(String story_ids[],int sprintId) {
		// TODO Auto-generated method stub
		
		String sql="update Story set sprint_id=? where story_id=?";
		int r=0;
		for(int i=0;i<story_ids.length;i++)
		{
			jdbcTemplate.update(sql,sprintId,Integer.parseInt(story_ids[i]));
		}
		
		
		
	}

	@Override
	public String getPriority(int story_id) {
	
		String sql="select priority_name from Priority where priority_id in(select priority_id from Story where story_id=?)";
		String name=jdbcTemplate.queryForObject(sql, String.class,story_id);
	return name;
	}

	@Override
	public String getStatus(int story_id) {
		String sql="select status_name from Status where status_id in(select status_id from Story where story_id=?)";
		String name=jdbcTemplate.queryForObject(sql, String.class,story_id);
	return name;
	}

	@Override
	public String getComplexity(int story_id) {
		String sql="select complexity_name from Complexity where complexity_id in(select complexity_id from Story where story_id=?)";
		String name=jdbcTemplate.queryForObject(sql, String.class,story_id);
		return name;
	}
	
	@Override
	public Story getStoryName(int story_id) {
		String sql = "select story_title from Story where story_id="+story_id;
		return jdbcTemplate.query(sql, new ResultSetExtractor<Story>() {

			@Override
			public Story extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Story story = new Story();
					story.setStory_title(rs.getString("story_title"));
					return story;
				}
				
				return null;
			}
			
		});
	}


}
